import sys
from PyQt5.QtWidgets import QApplication, QWidget, QLabel, QLineEdit, QPushButton, QVBoxLayout, QListWidget, \
    QListWidgetItem, QFileDialog, QMessageBox
from PyQt5.QtGui import QIcon
import pandas as pd
from openpyxl import Workbook


class ExcelComparerGUI(QWidget):
    def __init__(self):
        super().__init__()

        self.init_ui()

    def init_ui(self):
        self.setWindowTitle('Excel 比较工具')
        self.setWindowIcon(QIcon('icon.png'))  # 请替换为您的图标文件路径

        # 创建标签、输入框和按钮
        self.full_file_label = QLabel('全集 Excel 文件:')
        self.full_file_entry = QLineEdit(self)
        self.full_file_browse_button = QPushButton('浏览', self)
        self.full_file_browse_button.clicked.connect(self.browse_full_file)

        self.department_file_label = QLabel('部门 Excel 文件:')
        self.department_file_entry = QLineEdit(self)
        self.department_file_browse_button = QPushButton('浏览', self)
        self.department_file_browse_button.clicked.connect(self.browse_department_file)

        self.fields_label = QLabel('选择比较字段:')
        self.fields_list = QListWidget(self)

        self.compare_button = QPushButton('比较并保存', self)
        self.compare_button.clicked.connect(self.compare_files)

        # 设置提示文本
        self.full_file_entry.setPlaceholderText('选择全集 Excel 文件...')
        self.department_file_entry.setPlaceholderText('选择部门 Excel 文件...')

        # 使用布局管理器布局
        vbox = QVBoxLayout()
        vbox.addWidget(self.full_file_label)
        vbox.addWidget(self.full_file_entry)
        vbox.addWidget(self.full_file_browse_button)
        vbox.addWidget(self.department_file_label)
        vbox.addWidget(self.department_file_entry)
        vbox.addWidget(self.department_file_browse_button)
        vbox.addWidget(self.fields_label)
        vbox.addWidget(self.fields_list)
        vbox.addWidget(self.compare_button)

        self.setLayout(vbox)

        # 设置初始大小
        self.setGeometry(100, 100, 600, 400)

        self.show()

    def browse_full_file(self):
        file_path, _ = QFileDialog.getOpenFileName(self, '选择全集 Excel 文件', '', 'Excel 文件 (*.xlsx *.xls)')
        self.full_file_entry.setText(file_path)
        self.browse_department_and_populate_fields()

    def browse_department_file(self):
        file_path, _ = QFileDialog.getOpenFileName(self, '选择部门 Excel 文件', '', 'Excel 文件 (*.xlsx *.xls)')
        self.department_file_entry.setText(file_path)
        self.browse_department_and_populate_fields()

    def browse_department_and_populate_fields(self):
        full_file_path = self.full_file_entry.text()
        department_file_path = self.department_file_entry.text()
        self.populate_fields_list(full_file_path, department_file_path)

    def populate_fields_list(self, full_file_path, department_file_path):
        # 清空列表
        self.fields_list.clear()

        if full_file_path and department_file_path:
            full_df = pd.read_excel(full_file_path)
            department_df = pd.read_excel(department_file_path)

            # 找到两个文件都有的字段
            common_fields = set(full_df.columns) & set(department_df.columns)

            # 将列名添加到字段列表中
            for field in common_fields:
                item = QListWidgetItem(field)
                item.setCheckState(2)  # 设置为选中状态
                self.fields_list.addItem(item)

    def compare_files(self):
        full_file_path = self.full_file_entry.text()
        department_file_path = self.department_file_entry.text()

        if full_file_path and department_file_path:
            # 选择保存比较后文件的位置
            output_file_path, _ = QFileDialog.getSaveFileName(self, '保存比较后 Excel 文件', '', 'Excel 文件 (*.xlsx)')

            # 读取全集文件和部门文件
            full_df = pd.read_excel(full_file_path)
            department_df = pd.read_excel(department_file_path)

            # 获取用户选择的字段
            selected_fields = [self.fields_list.item(i).text() for i in range(self.fields_list.count()) if
                               self.fields_list.item(i).checkState()]

            # 使用 merge，left_only 和 right_only 标记两个文件的差异
            merged_df = pd.merge(full_df, department_df, on=selected_fields, how='left', indicator=True, sort=False)

            # 添加标识列
            merged_df['_merge'] = merged_df['_merge'].replace(
                {'both': 'both', 'left_only': 'left', 'right_only': 'right'})

            # 保存带有标识的结果到输出文件
            merged_df.to_excel(output_file_path, index=False, engine='openpyxl', sheet_name='Sheet1')

            QMessageBox.information(self, '成功', '文件比较成功。')
        else:
            QMessageBox.critical(self, '错误', '请填写所有字段。')


if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = ExcelComparerGUI()
    sys.exit(app.exec_())
